/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.informet.ipd.modelo.dao;

import com.informet.ipd.modelo.vo.RolUsuarioVO;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JOptionPane;

/**
 *
 * @author LAPTOP01
 */
public class UsuarioRolDao extends DBase {

    Connection conn = null;

    public UsuarioRolDao() {
        super();
    }

    private void setUsuarioRol(RolUsuarioVO bean, ResultSet rs) throws SQLException {
        bean.setNUM_USUARIOID(rs.getInt("NUM_USUARIOID"));
        bean.setTX_USUARIO_NAME(rs.getString("TX_USUARIO_NAME"));
        bean.setTX_USUARIO_PASS(rs.getString("TX_USUARIO_PASS"));
        bean.setTX_ROLNOM(rs.getString("TX_ROLNOM"));
        bean.setNUM_ROLACT(rs.getInt("NUM_ROLACT"));
         bean.setNUM_ROLID(rs.getInt("NUM_ROLID"));

    }
     private void setRol(RolUsuarioVO bean, ResultSet rs) throws SQLException {
        bean.setNUM_ROLID(rs.getInt("NUM_ROLID"));
        bean.setTX_ROLNOM(rs.getString("TX_ROLNOM"));
    }

    public List getlistTabla() throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<RolUsuarioVO> lista = new ArrayList<RolUsuarioVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = "select u.NUM_USUARIOID,u.TX_USUARIO_NAME,u.TX_USUARIO_PASS,r.TX_ROLNOM,ru.NUM_ROLACT,r.NUM_ROLID from ROL r inner join ROLUSUARIO ru "
                    + " on r.NUM_ROLID = ru.NUM_ROLID inner join usuario u"
                    + "    on u.NUM_USUARIOID=ru.NUM_USUARIOID";
            rs = stmt.executeQuery(sql);
            RolUsuarioVO bean = null;
            while (rs.next()) {
                bean = new RolUsuarioVO();
                setUsuarioRol(bean, rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }
     public List getlistRol() throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<RolUsuarioVO> lista = new ArrayList<RolUsuarioVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = "select NUM_ROLID , TX_ROLNOM from ROL";
            rs = stmt.executeQuery(sql);
            RolUsuarioVO bean = null;
            while (rs.next()) {
                bean = new RolUsuarioVO();
                setRol(bean, rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }



      public String spMan(int tipo,RolUsuarioVO bean) {
        String msj = "";
        try {

            conn = DriverManager.getConnection(url, usuario, password);
            CallableStatement cs = conn.prepareCall("{call MAN_ROLUSUARIO(?,?,?,?,?,?)}");
            cs.setInt(1, tipo);
            cs.setInt(2, bean.getNUM_USUARIOID());
            cs.setString(3, bean.getTX_USUARIO_NAME());
            cs.setString(4, bean.getTX_USUARIO_PASS());
            cs.setInt(5, bean.getNUM_ROLACT());
            cs.setInt(6, bean.getNUM_ROLID());

          
            cs.execute();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.toString());
            msj = e.toString();
            return msj;
        }
        return msj;
    }








//     public List getlistRol() {
//
//        List<Object[]> list = new ArrayList<Object[]>();
//        try {
//
//
//            if (conn == null) {
//                list = null;
//            } else {
//                Statement st = conn.createStatement();
//                ResultSet rs = st.executeQuery("select NUM_ROLID,TX_ROLNOM from ROL");
//                ResultSetMetaData rm = rs.getMetaData();
//                int numCols = rm.getColumnCount();
//
//
//                while (rs.next()) {
//                    Object[] fila = new Object[numCols];
//                    for (int i = 0; i < numCols; i++) {
//                        fila[i] = rs.getObject(i + 1);
//                    }
//                    list.add(fila);
//                }
//                conn.close();
//            }
//        } catch (SQLException e) {
//            list = null;
//             JOptionPane.showMessageDialog(null, "error");
//        }
//
//        return list;    // returna null si falla
//    }
//
//     public  Object[] getObject(String sql) throws SQLException{
//        List list = getlistRol();
//        Object[] fila = null;
//
//        if (list != null) {
//            if (list.size() > 1) {
//                fila = (Object[]) list.get(1);
//            }
//        }
//
//        return fila;
//    }
}
